from pathlib import Path
import requests
import pandas as pd
import time

# 创建存放下载文件的文件夹
output_dir = Path('问询函文件')
output_dir.mkdir(parents=True, exist_ok=True)

# 从工作簿中读取数据
df = pd.read_excel('上交所问询函.xlsx')

# 设置请求头
headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'}

# 遍历读取的数据
for r in range(df.shape[0]):
    # 从数据中提取一行并转换成字典
    row = df.iloc[r].to_dict()
    # 根据网址下载文件
    response = requests.get(url=row['文件网址'], headers=headers)
    # 构造文件保存路径
    file_path = output_dir / f"{r + 1}.{row['问询函标题']}.pdf"
    # 保存文件
    with open(file=file_path, mode='wb') as f:
        f.write(response.content)
    print(f'下载完成：{file_path}')
    # 适当暂停，以免触发反爬
    time.sleep(2)
